import xlrd
import pymysql
from self import self

class project_info_import:
    self.query1 = "delete from myapp3_project_info;"
    self.query = "delete from  image;"
    self.query2 = '''insert  into myapp3_project_info
    (project_name,project_area,project_sqaure,robot_id,project_people,is_arrives,
        regist_env,zeno_alias,usb_netcard_mac,is_power_on,trouble_shoot1,trouble_shoot2,trouble_shoot3,
        trouble_shoot4,trouble_shoot5,trouble_shoot6,trouble_shoot7,trouble_shoot8,trouble_shoot9,
        is_can_using,elevator_number,elevator_change,charge_loc,xunjiain_lianjie,guard_ac_number,
        daily_rep_num,map_routeid, robot_dt_lianddong,routineid,tryrun,sucai_up,warning_up,
        daily_rep_up_kehu,is_jiaojie,peixun_kehu,duijie_people,phone_num,live_area,other_info) 
    values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)   
    '''

    @staticmethod
    def real_import():
        db = pymysql.connect('192.168.28.110', 'root', '1234qwer!', 'robox_web')
        # db = pymysql.connect('127.0.0.1', 'root', '1234qwer!', 'sunguilou')
        cursor = db.cursor()
        File = xlrd.open_workbook("C:/Users/sunguilou/Desktop/test2.xlsx")
        sheet = File.sheet_by_index(0)
        nrows = sheet.nrows
        # cursor.execute(self.query)
        # cursor.execute(self.query1)
        for i in range(1, nrows):
            # try:
            project_name = sheet.cell_value(i, 0)
            project_area = sheet.cell_value(i, 1)
            project_sqaure = sheet.cell_value(i, 2)
            robot_id = sheet.cell_value(i, 3)
            project_people = sheet.cell_value(i, 4)
            is_arrives = sheet.cell_value(i, 5)
            regist_env = sheet.cell_value(i, 6)
            zeno_alias = sheet.cell_value(i, 7)
            usb_netcard_mac = sheet.cell_value(i, 8)
            is_power_on = sheet.cell_value(i, 9)
            trouble_shoot1 = sheet.cell_value(i, 10)
            trouble_shoot2 = sheet.cell_value(i, 11)
            trouble_shoot3 = sheet.cell_value(i, 12)
            trouble_shoot4 = sheet.cell_value(i, 13)
            trouble_shoot5 = sheet.cell_value(i, 14)
            trouble_shoot6 = sheet.cell_value(i, 15)
            trouble_shoot7 = sheet.cell_value(i, 16)
            trouble_shoot8 = sheet.cell_value(i, 17)
            trouble_shoot9 = sheet.cell_value(i, 18)
            is_can_using = sheet.cell_value(i, 19)
            elevator_number = sheet.cell_value(i, 20)
            elevator_change = sheet.cell_value(i, 21)
            charge_loc = sheet.cell_value(i, 22)
            xunjiain_lianjie = sheet.cell_value(i, 23)
            guard_ac_number = sheet.cell_value(i, 24)
            daily_rep_num = sheet.cell_value(i, 25)
            map_routeid = sheet.cell_value(i, 26)
            robot_dt_lianddong = sheet.cell_value(i, 27)
            routineid = sheet.cell_value(i, 28)
            tryrun = sheet.cell_value(i, 29)
            sucai_up = sheet.cell_value(i, 30)
            warning_up = sheet.cell_value(i, 31)
            daily_rep_up_kehu = sheet.cell_value(i, 32)
            is_jiaojie = sheet.cell_value(i, 33)
            peixun_kehu = sheet.cell_value(i, 34)
            duijie_people = sheet.cell_value(i, 35)
            phone_num = sheet.cell_value(i, 36)
            live_area = sheet.cell_value(i, 37)
            other_info = sheet.cell_value(i, 38)

            if project_name and project_area and robot_id:
               
                values = (project_name,project_area,project_sqaure,robot_id,project_people,is_arrives,
                regist_env,zeno_alias,usb_netcard_mac,is_power_on,trouble_shoot1,trouble_shoot2,trouble_shoot3,
                trouble_shoot4,trouble_shoot5,trouble_shoot6,trouble_shoot7,trouble_shoot8,trouble_shoot9,
                is_can_using,elevator_number,elevator_change,charge_loc,xunjiain_lianjie,guard_ac_number,
                daily_rep_num,map_routeid, robot_dt_lianddong,routineid,tryrun,sucai_up,warning_up,
                daily_rep_up_kehu,is_jiaojie,peixun_kehu,duijie_people,phone_num,live_area,other_info)

                print(values)
                cursor.execute(self.query2, values)
        cursor.close()
        db.commit()
        db.close()

project_info_import.real_import()